﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;

namespace QuanlyDanhsachThiCapPhong
{
    class MonThi
    {
        string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\\Database\\Data.dat';Extended Properties=Excel 8.0";
        public String getSBD(String khoi, String monthi, String hocsinhid)
        {
            String id = "";
            MonThi func = new MonThi();
            OleDbConnection conn = new OleDbConnection(connstr);
            string sql = "SELECT ID FROM [MonThi$] WHERE [Khối] =" + khoi + " AND [Môn thi] = '" + monthi + "'";
            conn.Open();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                id = dr.GetDouble(0).ToString();
            }
            dr.Close();
            conn.Close();
            return id + hocsinhid;
        }
        public List<string> getIdTheoBuoi(int khoi, string buoi_thi)
        {
            List<string> listId = new List<string>();
            String id = "";
            MonThi func = new MonThi();
            OleDbConnection conn = new OleDbConnection(connstr);
            string sql = "SELECT Left(ID,2) FROM [MonThi$] WHERE [Khối] =" + khoi + " AND [Buổi Thi] LIKE '%" + buoi_thi + "%'";
            conn.Open();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                id = dr.GetString(0);
                listId.Add(id);
            }
            dr.Close();
            conn.Close();
            return listId;
        }
    }
}
